package com.zlm.realtime.utils

import com.alibaba.fastjson.JSONObject

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet, ResultSetMetaData, SQLException}
import java.util.Properties
import scala.collection.mutable.ListBuffer

/**
 * Author: Harbour 
 * Date: 2021-04-23 22:26
 * Desc: 
 */
object MySQLUtils {
    def queryAll(sql: String): ListBuffer[JSONObject] = {

        // step 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver")

        // step 2. 建立连接
        val conn: Connection = DriverManager.getConnection(
            "jdbc:mysql://hadoop201/mall_rs?characterEncoding=utf-8&useSSL=false",
            "root",
            "root"
        )

        // step 3. 得到数据库操作对象
        val preStat: PreparedStatement = conn.prepareStatement(sql)

        // step 4. 执行SQL，处理结果集
        val jsonList = new ListBuffer[JSONObject]()
        val resultSet: ResultSet = preStat.executeQuery()
        val metaData: ResultSetMetaData = resultSet.getMetaData
        while (resultSet.next()) {
            val jSONObject = new JSONObject()
            // JDBC 从1开始计数
            for (i <- 1 to metaData.getColumnCount) {
                jSONObject.put(metaData.getColumnName(i), resultSet.getObject(i))
            }
            jsonList.append(jSONObject)
        }

        // step 5. 关闭资源
        close(resultSet, preStat, conn)
        jsonList
    }

    def close(resultSet: ResultSet, preparedStatement: PreparedStatement, connection: Connection): Unit = {
        try {
            if (resultSet != null) resultSet.close()
            if (preparedStatement != null) preparedStatement.close()
            if (connection != null) connection.close()
        } catch {
            case e: SQLException => e.printStackTrace()
        }
    }
}
